package domain;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class AlternativePair extends Comparison{
	private Alternative subject;
	private Alternative object;
	private Criteria criteria;
	
	public Alternative getSubject() {
		return subject;
	}
	public void setSubject(Alternative subject) {
		this.subject = subject;
	}
	public Alternative getObject() {
		return object;
	}
	public void setObject(Alternative object) {
		this.object = object;
	}
	public Criteria getCriteria() {
		return criteria;
	}
	public void setCriteria(Criteria criteria) {
		this.criteria = criteria;
	}
	
	//If exist then update, if not then insert
	public void insert() throws SQLException{
		boolean isExist = false;
		Connection conn = getConnection();
		PreparedStatement ps = null;
		StringBuffer sqlSelect = new StringBuffer();
		sqlSelect.append("select id from comparison where criteria_id = ? and sub_alternative_id = ? and ob_alternative_id = ? ");
		int index=1;
		 ps = conn.prepareStatement(sqlSelect.toString());
		 	ps.setLong(index++, this.criteria.getId());
		 	ps.setLong(index++, this.subject.getId());
		 	ps.setLong(index++, this.object.getId());
		 ResultSet rs = ps.executeQuery();
		 
		 if(rs.next()){
			 isExist = true;
		 }
		 
		 if(!isExist){ // INSERT
			 StringBuffer sql = new StringBuffer();
				 sql.append("insert into comparison(score, sub_alternative_id, ob_alternative_id, sub_criteria_id, ob_criteria_id, type, criteria_id, project_id) ");
				 sql.append(" values(?, ?, ?, ?, ?, ?, ?, ?)");
			 index =1;
			 ps = conn.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			 	ps.setDouble(index++, this.score);
			 	ps.setLong(index++, this.subject.getId());
			 	ps.setLong(index++, this.object.getId());
			 	ps.setInt(index++, 0);
			 	ps.setInt(index++, 0);
			 	ps.setString(index++, this.type);
			 	ps.setLong(index++, this.criteria.getId());
			 	ps.setInt(index++, 0);
			 	
			 ps.executeUpdate();
			 
			 ResultSet resultSet = ps.getGeneratedKeys();
				if (resultSet.next()) {
					long generatedid = resultSet.getLong(1);
					ps.close();
					conn.close();
					this.id = generatedid;
				} else {
					 System.out.println("Cannot Get Genereated ID!!!!");
				}
		 }else{// UPDATE
			 StringBuffer sql = new StringBuffer();
				 sql.append("update comparison set score = ? where criteria_id = ? and sub_alternative_id = ? and ob_alternative_id = ? ");
			 index =1;
			 ps = conn.prepareStatement(sql.toString());
			 	ps.setDouble(index++, this.score);
			 	ps.setLong(index++, this.criteria.getId());
			 	ps.setLong(index++, this.subject.getId());
			 	ps.setLong(index++, this.object.getId());
			 	
			ps.executeUpdate();
			ps.close();
			conn.close();
		 }
	}
	
	public static List<AlternativePair> findByCriteriaId(long criteriaId) throws SQLException {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		 StringBuffer sql = new StringBuffer();
			 sql.append("select id, score, sub_alternative_id, ob_alternative_id from comparison where criteria_id = ?");
		 ps = conn.prepareStatement(sql.toString());
		 int index=1;
		 	ps.setLong(index++, criteriaId);
		 	
		 ResultSet rs = ps.executeQuery();
		 
		 List<AlternativePair> pairs = new ArrayList<AlternativePair>();
		 while(rs.next()){
			 AlternativePair pair = new AlternativePair();
				 pair.setId(rs.getLong("id"));
				 pair.setScore(rs.getDouble("score"));
				 pair.setSubject(Alternative.find(rs.getLong("sub_alternative_id")));
				 pair.setObject(Alternative.find(rs.getLong("ob_alternative_id")));
			pairs.add(pair);
		 }
		 
		 ps.close();
		 conn.close();
		 
		 return pairs;
	}
	
}
